/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import view.dangnhap;
import view.home;
public class Conection {
    private   Connection con;
    private static ResultSet rs = null;
    private static ResultSetMetaData rsmd = null;
    private static Statement stmt = null;
    public static Vector header;
    public static Vector data;
    private final String server = "localhost";  // const
    private final int port = 1433;
    private final String databaseName = "QL_VPP";
    private final String username = "sa";
    private final String password = "1234";
    
    dangnhap dangnhap;
    home home;
    public Conection(dangnhap dangnhap)
    {
    this.dangnhap=dangnhap;
    }
     public Conection(home home)
     {
    this.home=home;
     }
     public Conection(dangnhap dangnhap,home home)
     {
         this.dangnhap=dangnhap;
          this.home=home;
     }
     public Connection conect()
     {
           try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch(ClassNotFoundException e) {
            System.out.println("Khong tim thay Driver cho SQL Server 2005");
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(getURLConnection());
        } catch(SQLException e) {
            System.out.println("Khong ket noi duoc voi database");
        }
        return con;
    }
    private String getURLConnection() {
        return "jdbc:sqlserver://" + server + ":" + port +
                ";databaseName=" + databaseName +
                ";User=" + username +
                ";Password=" + password;
    }
    ResultSet executeSQL(String querySQL) throws SQLException{
        Statement s = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = s.executeQuery(querySQL);
        return rs;
    
     }
      public  void executequery(String sql) {
        try {
            con = conect();
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            rsmd = rs.getMetaData();
            int col = rsmd.getColumnCount();
            header = new Vector();
            for (int i = 1; i <= col; i++) {
                header.add(rsmd.getColumnName(i));
            }
            data = new Vector();
            while (rs.next()) {
                Vector temp = new Vector();

                for (int i = 1; i <= col; i++) {
                    temp.add(rs.getString(i));
                }
                data.add(temp);
            }


        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "There was an SQL error!");
            //System.exit(0);
            return;

        } finally {
            disconnect(rs);
            disconnect(stmt);
            disconnect(con);
        }

    }
       public void disconnect(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
                System.out.println("Statement Closed!");
            } catch (SQLException ex) {
                Logger.getLogger(Conection.class.getName()).log(Level.SEVERE, null, ex);
                JOptionPane.showMessageDialog(null, "There was an SQL error!");
                // System.exit(0);
                return;
            }
        }
    }
          public void disconnect(Connection con) {
        if (con != null) {
            try {
                con.close();
                System.out.println("Disconnect");
            } catch (SQLException ex) {
                Logger.getLogger(Conection.class.getName()).log(Level.SEVERE, null, ex);
                JOptionPane.showMessageDialog(null, "There was an SQL error!");
                //  System.exit(0);
                return;
            }
        }
    }
          
   public void disconnect(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
                System.out.println("ResultSet Closed!");
            } catch (SQLException ex) {

                Logger.getLogger(Conection.class.getName()).log(Level.SEVERE, null, ex);
                JOptionPane.showMessageDialog(null, "There was an SQL error!");
                // System.exit(0);
                return;
            }
        }
    }
}
